﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using it_security_project.App_Code.BusinessLogicLayer;
using System.Data.SqlClient;
using System.Data;

namespace it_security_project.App_Code.DataAccessLayer
{
    public class ThreadDAL : DALBase
    {
        public List<Thread> GetThreads()
        {       
            using (SqlConnection conn = CreateConnection())
            {
                try
                {
                    List<Thread> threads = new List<Thread>(100);
                    SqlCommand cmd = new SqlCommand("dbo.usp_GetAllThreads", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int ThreadIDIndex = reader.GetOrdinal("ThreadID");
                        int AuthorIndex = reader.GetOrdinal("Username");
                        int TitleIndex = reader.GetOrdinal("Title");
                        int TopicIndex = reader.GetOrdinal("Topic");

                        while (reader.Read())
                        {
                            threads.Add(new Thread
                            {
                                ThreadID = reader.GetInt32(ThreadIDIndex),
                                Author = reader.GetString(AuthorIndex),
                                Title = reader.GetString(TitleIndex),
                                Topic = reader.GetString(TopicIndex),
                            });

                        }
                        threads.TrimExcess();
                        return threads;
                   }
                }
                catch
                {
                    throw new ApplicationException("An error has occured in the data access layer.");
                }
            }
        }

        public List<Thread> GetThreadsByUsername(string username)
        {
            using (SqlConnection conn = CreateConnection())
            {
                try
                {
                    List<Thread> threads = new List<Thread>(100);
                    SqlCommand cmd = new SqlCommand("dbo.usp_GetThreadsByUsername", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@Username", username);

                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int ThreadIDIndex = reader.GetOrdinal("ThreadID");
                        int TitleIndex = reader.GetOrdinal("Title");
                        int TopicIndex = reader.GetOrdinal("Topic");

                        while (reader.Read())
                        {
                            threads.Add(new Thread
                            {
                                ThreadID = reader.GetInt32(ThreadIDIndex),
                                Author = username,
                                Title = reader.GetString(TitleIndex),
                                Topic = reader.GetString(TopicIndex),
                            });
                        }
                        threads.TrimExcess();
                        return threads;
                    }
                }
                catch
                {
                    throw new ApplicationException("An error has occured in the data access layer.");
                }
            }
        }

        public Thread GetThreadById(int threadID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                try
                {
                    Thread thread = new Thread();
                    SqlCommand cmd = new SqlCommand("dbo.usp_GetThreadById", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ThreadID", threadID);

                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int ThreadIDIndex = reader.GetOrdinal("ThreadID");
                        int AuthorIndex = reader.GetOrdinal("Username");
                        int TitleIndex = reader.GetOrdinal("Title");
                        int TopicIndex = reader.GetOrdinal("Topic");

                        while (reader.Read())
                        {
                            thread.ThreadID = reader.GetInt32(ThreadIDIndex);
                            thread.Author = reader.GetString(AuthorIndex);
                            thread.Title = reader.GetString(TitleIndex);
                            thread.Topic = reader.GetString(TopicIndex);
                        }
                        return thread;
                    }
                }
                catch
                {
                    throw new ApplicationException("An error has occured in the data access layer.");
                }
            }
        }

        internal List<Comment> GetCommentsOnThreadId(int id)
        {
            using (SqlConnection conn = CreateConnection())
            {
                try
                {
                    List<Comment> comments = new List<Comment>();
                    SqlCommand cmd = new SqlCommand("dbo.usp_GetCommentsByThreadID", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ThreadID", id);

                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int CommentIdIndex = reader.GetOrdinal("CommentID");
                        int ContentIndex = reader.GetOrdinal("Comment");
                        int UsernameIndex = reader.GetOrdinal("Username");

                        while (reader.Read())
                        {
                            comments.Add(new Comment()
                            {
                                CommentID = reader.GetInt32(CommentIdIndex),
                                Content = reader.GetString(ContentIndex),
                                Username = reader.GetString(UsernameIndex)
                            });
                        }
                        return comments;
                    }
                }
                catch
                {
                    throw new ApplicationException("An error has occured in the data access layer.");
                }
            }
            
        }

        internal void AddComment(int userID, string comment, int threadID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                //try
                //{
                SqlCommand cmd = new SqlCommand("dbo.usp_AddComment", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
                cmd.Parameters.Add("@Comment", SqlDbType.NVarChar, 500).Value = comment;
                cmd.Parameters.Add("@ThreadID", SqlDbType.Int).Value = threadID;
                conn.Open();

                cmd.ExecuteNonQuery();

                //member.MemberID = (int)cmd.Parameters["@MemberID"].Value;
                //}
                //catch
                //{
                //throw new Exception("Error occured in the Data Access Layer");
                //}
            }
        }

        internal void AddThread(Thread thread)
        {
            MemberDAL MD = new MemberDAL();
            int memberid = MD.GetMemberByUsername(thread.Author).MemberID;

            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand("dbo.usp_AddThread", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MemberID", SqlDbType.Int).Value = memberid;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 50).Value = thread.Title;
                cmd.Parameters.Add("@Topic", SqlDbType.NVarChar, 500).Value = thread.Topic;

                conn.Open();

                cmd.ExecuteNonQuery();
            }
        }

        internal void DeleteThread(int threadID)
        {
            using (SqlConnection conn = CreateConnection())
            {
                try
                {
                    Thread thread = new Thread();
                    SqlCommand cmd = new SqlCommand("dbo.usp_DeleteThreadByID", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ThreadID", threadID);

                    conn.Open();

                    cmd.ExecuteNonQuery();
                }
                catch
                {
                    throw new ApplicationException("An error has occured in the data access layer.");
                }
            }
        }
    }
}